﻿using Dapper;
using OMG.Fill.Frunit.Model;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace OMG.Fill.Frunit.Service
{
    /// <summary>
    /// 技术推广人员情况表T_FruTecPromotion通用操作类
    /// </summary>
    public class FruTecPromotions
    {
        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(FruTecPromotionEntity model) {
            ResResult<int> obj = new ResResult<int>();

            string strSql = @"INSERT INTO dbo.T_FruTecPromotion";
            strSql +="(";
            strSql +="Id,";
            strSql +="ReportId,";
            strSql +="InstituNum,";
            strSql +="InstituNum_City,";
            strSql +="InstituNum_Area,";
            strSql +="InstituNum_town,";
            strSql +="PeopleNum,";
            strSql += "PeopleNum_County,";
            strSql += "PeopleNum_City,";
            strSql += "PeopleNum_man,";
            strSql +="PeopleNum_woman,";
            strSql +="PeopleNum_age1,";
            strSql +="PeopleNum_age2,";
            strSql +="PeopleNum_age3,";
            strSql +="PeopleNum_age4,";
            strSql +="PeopleNum_xlss,";
            strSql +="PeopleNum_xlbk,";
            strSql +="PeopleNum_xldz,";
            strSql +="PeopleNum_xlzz,";
            strSql +="PeopleNum_xlqt,";
            strSql +="PeopleNum_zczgj,";
            strSql +="PeopleNum_zcfgj,";
            strSql +="PeopleNum_zczj,";
            strSql +="PeopleNum_zccj,";
            strSql +="PeopleNum_zcqt,";
            strSql +="AddUser,";
            strSql +="AddTime,";
            strSql +="UpdateUser,";
            strSql +="UpdateTime)";
            strSql +=" values(";
            strSql +="@Id,";
            strSql +="@ReportId,";
            strSql +="@InstituNum,";
            strSql +="@InstituNum_City,";
            strSql +="@InstituNum_Area,";
            strSql +="@InstituNum_town,";
            strSql +="@PeopleNum,";
            strSql +="@PeopleNum_County,";
            strSql +="@PeopleNum_City,";
            strSql +="@PeopleNum_man,";
            strSql +="@PeopleNum_woman,";
            strSql +="@PeopleNum_age1,";
            strSql +="@PeopleNum_age2,";
            strSql +="@PeopleNum_age3,";
            strSql +="@PeopleNum_age4,";
            strSql +="@PeopleNum_xlss,";
            strSql +="@PeopleNum_xlbk,";
            strSql +="@PeopleNum_xldz,";
            strSql +="@PeopleNum_xlzz,";
            strSql +="@PeopleNum_xlqt,";
            strSql +="@PeopleNum_zczgj,";
            strSql +="@PeopleNum_zcfgj,";
            strSql +="@PeopleNum_zczj,";
            strSql +="@PeopleNum_zccj,";
            strSql +="@PeopleNum_zcqt,";
            strSql +="@AddUser,";
            strSql +="@AddTime,";
            strSql +="@UpdateUser,";
            strSql +="@UpdateTime)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id= CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增技术推广人员情况表T_FruTecPromotion对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        public virtual ResResult<int> UpdateInfo(FruTecPromotionEntity model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_FruTecPromotion set ";
            strSql+="ReportId = @ReportId,";
            strSql+="InstituNum = @InstituNum,";
            strSql+="InstituNum_City = @InstituNum_City,";
            strSql+="InstituNum_Area = @InstituNum_Area,";
            strSql+="InstituNum_town = @InstituNum_town,";
            strSql+="PeopleNum = @PeopleNum,";
            strSql+= "PeopleNum_City = @PeopleNum_City,";
            strSql+= "PeopleNum_County = @PeopleNum_County,";
            strSql +="PeopleNum_man = @PeopleNum_man,";
            strSql+="PeopleNum_woman = @PeopleNum_woman,";
            strSql+="PeopleNum_age1 = @PeopleNum_age1,";
            strSql+="PeopleNum_age2 = @PeopleNum_age2,";
            strSql+="PeopleNum_age3 = @PeopleNum_age3,";
            strSql+="PeopleNum_age4 = @PeopleNum_age4,";
            strSql+="PeopleNum_xlss = @PeopleNum_xlss,";
            strSql+="PeopleNum_xlbk = @PeopleNum_xlbk,";
            strSql+="PeopleNum_xldz = @PeopleNum_xldz,";
            strSql+="PeopleNum_xlzz = @PeopleNum_xlzz,";
            strSql+="PeopleNum_xlqt = @PeopleNum_xlqt,";
            strSql+="PeopleNum_zczgj = @PeopleNum_zczgj,";
            strSql+="PeopleNum_zcfgj = @PeopleNum_zcfgj,";
            strSql+="PeopleNum_zczj = @PeopleNum_zczj,";
            strSql+="PeopleNum_zccj = @PeopleNum_zccj,";
            strSql+="PeopleNum_zcqt = @PeopleNum_zcqt,";
            strSql+="AddUser = @AddUser,";
            strSql+="AddTime = @AddTime,";
            strSql+="UpdateUser = @UpdateUser,";
            strSql+="UpdateTime = @UpdateTime";
            strSql+=" where Id = @Id";
           try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        ReportServices reportServices = new ReportServices();
                        reportServices.UpStatus(model.ReportId, "2", "");
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "更新成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "更新失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新技术推广人员情况表T_FruTecPromotion对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        public virtual ResResult<int> DeleteInfo(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_FruTecPromotion where Id = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除技术推广人员情况表T_FruTecPromotion对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        public virtual ResResult<int> DeleteBatch(string[] ids)
        {
            ResResult<int> obj = new ResResult<int>();
            string strIds = string.Join(",", ids.Select(x => x.ToString()).ToArray());
            string strSql = @"delete from dbo.T_FruTecPromotion where Id in @ids";
            var dynamicSqlParam = new DynamicParameters();
            dynamicSqlParam.Add("ids", ids.ToArray(), null, null, null);
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, dynamicSqlParam);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取数据字典表批量删除技术推广人员情况表T_FruTecPromotion表失败", ex);
                obj.message = ex.ToString().Trim();
                obj.status = DefaultKey.ErrorResult;
                return obj;
            }
        }
        /// <summary>
        /// 获取今往年数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<FruTecPromotionViewModel> GetModel(VegNowModel model)
        {
            ResResult<FruTecPromotionViewModel> obj = new ResResult<FruTecPromotionViewModel>();
            FruTecPromotionEntity Entity = new FruTecPromotionEntity();
           FruTecPromotionViewModel viewModel = new FruTecPromotionViewModel();
            DictServices dictServices = new DictServices();
            var paramlist = new DynamicParameters();
            string strSqlReport = "";
            if (model != null)
            {
                if (!string.IsNullOrEmpty(model.AreaCode) && !string.IsNullOrEmpty(model.ReportCode) && !string.IsNullOrEmpty(model.FillInYear))
                {
                    string FillInYear = dictServices.GetKey(model.FillInYear);
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("ReportCode", model.ReportCode);
                    paramlist.Add("FillInYear", FillInYear);
                    int OldFillInYear = Convert.ToInt32(FillInYear) - 1;
                    if (OldFillInYear < 0)
                    {
                        OldFillInYear = 0;
                    }
                    paramlist.Add("OldFillInYear", OldFillInYear.ToString());
                    string strNowSql = "SELECT vf.Id,vf.ReportId,InstituNum,InstituNum_City,InstituNum_Area,InstituNum_town,PeopleNum,PeopleNum_City,PeopleNum_County,PeopleNum_man,PeopleNum_woman,PeopleNum_age1,PeopleNum_age2"
                                    + ",PeopleNum_age3,PeopleNum_age4,PeopleNum_xlss,PeopleNum_xlbk,PeopleNum_xldz,PeopleNum_xlzz,PeopleNum_xlqt,PeopleNum_zczgj,PeopleNum_zcfgj,PeopleNum_zczj,PeopleNum_zccj,PeopleNum_zcqt"
                                    + ",vf.AddUser,vf.AddTime,vf.UpdateUser,vf.UpdateTime,ReportCode,Opinion,Status as ReportStatus  FROM T_Report r LEFT JOIN T_FruTecPromotion vf on vf.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @FillInYear and AreaCode = @AreaCode ";
                    string strOldSql = "SELECT vf.Id,vf.ReportId,InstituNum as OldInstituNum,InstituNum_City as OldInstituNum_City,InstituNum_Area as OldInstituNum_Area,InstituNum_town as OldInstituNum_town,PeopleNum as OldPeopleNum"
                                    + ",PeopleNum_City as OldPeopleNum_City,PeopleNum_County as OldPeopleNum_Area,PeopleNum_man as OldPeopleNum_man,PeopleNum_woman as OldPeopleNum_woman,PeopleNum_age1 as OldPeopleNum_age1,PeopleNum_age2 as OldPeopleNum_age2"
                                    + ",PeopleNum_age3 as OldPeopleNum_age3,PeopleNum_age4 as OldPeopleNum_age4,PeopleNum_xlss as OldPeopleNum_xlss,PeopleNum_xlbk as OldPeopleNum_xlbk,PeopleNum_xldz as OldPeopleNum_xldz,PeopleNum_xlzz as OldPeopleNum_xlzz"
                                    + ",PeopleNum_xlqt as OldPeopleNum_xlqt,PeopleNum_zczgj as OldPeopleNum_zczgj,PeopleNum_zcfgj as OldPeopleNum_zcfgj,PeopleNum_zczj as OldPeopleNum_zczj,PeopleNum_zccj as OldPeopleNum_zccj,PeopleNum_zcqt as OldPeopleNum_zcqt"
                                    + ",vf.AddUser as OldAddUser,vf.AddTime as OldAddTime,vf.UpdateUser as OldUpdateUser,vf.UpdateTime as OldUpdateTime,ReportCode,Status as OldReportStatus  FROM T_Report r LEFT JOIN T_FruTecPromotion vf on vf.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @OldFillInYear and AreaCode = @AreaCode ";
                    strSqlReport = "SELECT * FROM ((" + strNowSql + ")t LEFT JOIN  (" + strOldSql + ")tt ON t.ReportCode = tt.ReportCode)";

                    //判断选择的年度与上一年度是否生成过报表
                    if (CheckRepCreate(model.ReportCode, model.AreaCode, FillInYear) && CheckRepCreate(model.ReportCode, model.AreaCode, OldFillInYear.ToString()))
                    {
                        obj.model = viewModel;
                        obj.status = -1;
                        obj.message = model.FillInYear + "与上一年的报表没有填报数据！！";
                        return obj;
                    }
                    //判断本年是否生成过报表
                    else if (CheckRepCreate(model.ReportCode, model.AreaCode, FillInYear))
                    {
                        var dbCon = ConnectFactory.GetOpenConnection();
                        obj.model = dbCon.Query<FruTecPromotionViewModel>(strOldSql, paramlist).FirstOrDefault();
                        obj.status = -2;
                        obj.message = model.FillInYear + "的报表没有填报数据！！";
                        dbCon.Dispose();//数据库连接释放
                        return obj;
                    }
                    //判断上一年是否生成过报表
                    //else if (CheckRepCreate(model.ReportCode, model.AreaCode, OldFillInYear.ToString()))
                    //{
                    //    var dbCon = ConnectFactory.GetOpenConnection();
                    //    obj.model = dbCon.Query<VegTecPromotionViewModel>(strNowSql, paramlist).FirstOrDefault();
                    //    obj.status = -3;
                    //    obj.message = "上一年的报表没有填报数据！！";
                    //    dbCon.Dispose();//数据库连接释放
                    //    return obj;
                    //}
                    else
                    {
                        //获取报表Id
                        Entity.ReportId = GetReportId(model.ReportCode, model.AreaCode, FillInYear);
                        //判断表T_VegFacility是否初始化过
                        if (CheckRepCount(Entity.ReportId))
                        {
                            if (AddInfo(Entity).status > 0)//报表初始化
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    //查询去年数据
                                    obj.model = dbCon.Query<FruTecPromotionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 1;//初始化成功status为1
                                    obj.message = "T_VegTecPromotion报表初始化成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                        }
                        else
                        {
                            try
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    obj.model = dbCon.Query<FruTecPromotionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 2;//成功状态为2
                                    obj.message = "获取T_FruTecPromotion报表今往年数据成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                            catch (Exception ex)
                            {
                                obj.status = 0; //失败状态
                                obj.message = ex.ToString().Trim();
                                LogHelper.ErrorLog("获取T_FruTecPromotion报表今往年数据失败", ex);
                            }
                        }
                    }
                }
            }
            return obj;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_FruTecPromotion where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表1蔬菜生产实体情况表T_FruTecPromotion对象信息失败", ex);
                return false;
            }
        }
        /// <summary>
        /// 根据地区、报表代码、填报年度判断是否生成过报表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CheckRepCreate(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select count(1) from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_FruTecPromotion没有生成过", ex);
                return false;
            }
        }
        /// <summary>
        /// 根据地区、报表代码、填报年度获取报表Id
        /// </summary>
        /// <param name="ReportCode"></param>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public string GetReportId(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    return dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault().Id;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_FruTecPromotion没有生成过", ex);
                return "0";
            }
        }
        /// <summary>
        /// 获取全部列表
        /// </summary>
        /// <returns></returns>
        public virtual ResResult<List<FruTecPromotionEntity>> GetList()
        {
            ResResult<List<FruTecPromotionEntity>> obj = new ResResult<List<FruTecPromotionEntity>>();
            string strSql = @"select * from dbo.T_FruTecPromotion";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.model = dbCon.Query<FruTecPromotionEntity>(strSql).ToList();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新技术推广人员情况表T_FruTecPromotion对象信息失败", ex);
                return obj;
            }
        }
    }
}